﻿using SQLEditor.admin.entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;

namespace SQLEditor.admin.service
{
    public class MsSqlAnswer:IAnswer
    {

        private string connStr = "";
        public void answerCreateIndex(SqlAnswer qa, int questionId, string answerSql)
        {
            /*
            int point = 0;
            int userId = qa.userId;
            if (answerSql == qa.refAnswer)
            {
                //与参考答案相同
                qa.status = SqlAnswer.SUCCESS;
                qa.statusStr = SqlAnswer.SUCCESS_STR1;

                try
                {
                    string json = "[]";
                    qa.jsonAnswer = json;
                    qa.jsonRefAnswer = json;
                    point = qa.point;
                }
                catch (Exception ex)
                {
                    qa.status = SqlAnswer.FAIL_COMPILE;
                    qa.statusStr = "参考答案语法错误:" + ex.Message;
                }
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            try
            {
                if (userId != 1)
                {
                    filterString(answerSql);
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "包含非法关键字:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            string refsql = qa.refAnswer;
            string answersql = answerSql;
            string reftablename = getTableNameIndex(refsql);
            string indexName = getIndexName(refsql);
            string answertablename = getTableNameIndex(answersql);
            if (answertablename.Length == 0)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "创建索引的语法错误！";
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            string reftbPrefix = "thinkgoref" + qa.userId + "_";
            string anstbPrefix = "thinkgoans" + qa.userId + "_";
            refsql = refsql.Replace(reftablename, reftbPrefix + reftablename);
            answersql = answersql.Replace(answertablename, anstbPrefix + answertablename);

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tr = conn.BeginTransaction();
                try
                {
                    SQLHelper.ExcuteSQL(conn, tr, refsql, null, CommandType.Text);
                    DataTable refdt = SQLHelper.GetTable(conn, tr, "p_get_table_indexes", new SqlParameter[] {
                        new SqlParameter("@tablename",reftablename)
                    }, CommandType.StoredProcedure); qa.jsonRefAnswer = ToJson(refdt);
                    SQLHelper.ExcuteSQL(conn, tr, answersql, null, CommandType.Text);
                    DataTable ansdt = SQLHelper.GetTable(conn, tr, answertableSQL, null, CommandType.Text);
                    qa.jsonAnswer = ToJson(ansdt);
                    SqlParameter p1 = new SqlParameter("@tbname1", reftbPrefix + reftablename);
                    SqlParameter p2 = new SqlParameter("@tbname2", anstbPrefix + answertablename);
                    DataTable dt = SQLHelper.GetTable(conn, tr, "p_compare_table_structure", new SqlParameter[] { p1, p2 }, CommandType.StoredProcedure);
                    if (dt.Rows.Count < 1)
                    {
                        qa.status = SqlAnswer.SUCCESS;
                        qa.statusStr = SqlAnswer.SUCCESS_STR2;
                    }
                    else
                    {
                        string str = "比较结果:";
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (str.Length > "比较结果:".Length) str += ";";
                            str += dr[0].ToString();

                        }
                        qa.status = SqlAnswer.FAIL_RESULT;
                        qa.statusStr = str;
                    }

                    tr.Rollback();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    string msg = ex.Message.Replace(reftbPrefix, "");
                    msg = msg.Replace(anstbPrefix, "");
                    throw new Exception(msg);
                }
            }
            point = qa.point;
            saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
            return;
            */
        }

        public void answerCreateView(SqlAnswer qa, int questionId, string answerSql)
        {
            int point = 0;
            int userId = qa.userId;
            string refsql = qa.refAnswer;
            string preSQL = "";
            try
            {
                //int x = qa.refAnswer.IndexOf(";");
                //refsql = qa.refAnswer.Substring(0, x).Trim();
                //preSQL = qa.refAnswer.Substring(x + 1).Trim();
                if (answerSql == refsql)
                {
                    //与参考答案相同
                    qa.status = SqlAnswer.SUCCESS;
                    qa.statusStr = SqlAnswer.SUCCESS_STR1;

                    string json = "[]";
                    qa.jsonAnswer = json;
                    qa.jsonRefAnswer = json;
                    point = qa.point;
                    saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                    return;
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "参考答案语法错误:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }

            try
            {
                if (userId != 1)
                {
                    AnswerUtils.filterString(answerSql);
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "包含非法关键字:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }

            string answersql = answerSql;

            string reftablename = getViewName(refsql);
            string answertablename = getViewName(answersql);
            if (answertablename.Length == 0)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "语法错误！";
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            if(reftablename != answertablename)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "视图名称不正确！";
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            string reftbPrefix = "thinkgoref" + qa.userId + "_";
            string anstbPrefix = "thinkgoans" + qa.userId + "_";

            //修改之前先创建表的SQL
            //string preCreateRefTable = preSQL.Replace(reftablename, reftbPrefix + reftablename);
            //string preCreateAnsTable = preSQL.Replace(reftablename, anstbPrefix + reftablename);
            //创建视图要执行的SQL语句
            refsql = refsql.Replace(reftablename, reftbPrefix + reftablename);
            answersql = answersql.Replace(answertablename, anstbPrefix + answertablename);

            //查询表结构的SQL语句
            string reftableSQL = "select * from " + reftbPrefix + reftablename + "";
            string answertableSQL = "select * from " + anstbPrefix + reftablename + "";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tr = conn.BeginTransaction();
                try
                {
                    //建参考表
                   // SQLHelper.ExcuteSQL(conn, tr, preCreateRefTable, null, CommandType.Text);
                    //建要修改的表
                   // SQLHelper.ExcuteSQL(conn, tr, preCreateAnsTable, null, CommandType.Text);
                    //执行参考的SQL
                    SQLHelper.ExcuteSQL(conn, tr, refsql, null, CommandType.Text);
                    DataTable refdt = SQLHelper.GetTable(conn, tr, reftableSQL, null, CommandType.Text);
                    qa.jsonRefAnswer = AnswerUtils.ToJson(refdt);
                    //执行作答的SQL
                    SQLHelper.ExcuteSQL(conn, tr, answersql, null, CommandType.Text);
                    DataTable ansdt = SQLHelper.GetTable(conn, tr, answertableSQL, null, CommandType.Text);
                    qa.jsonAnswer = AnswerUtils.ToJson(ansdt);
                    DataTable dt1 = ansdt.Copy();
                    DataTable dt2 = refdt.Copy();

                    tr.Rollback();
                    conn.Close();

                    if (AnswerUtils.CompareDataTable(dt1, dt2))
                    {
                        qa.status = SqlAnswer.SUCCESS;
                        qa.statusStr = SqlAnswer.SUCCESS_STR2;
                        point = qa.point;
                    }
                    else
                    {
                        qa.status = SqlAnswer.FAIL_RESULT;
                        qa.statusStr = SqlAnswer.FAIL_RESULT_STR;
                        point = 0;
                    }
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    string msg = ex.Message.Replace(reftbPrefix, "");
                    msg = msg.Replace(anstbPrefix, "");
                    throw new Exception(msg);
                }
            }
            
            saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
        }

        public void answerInsert(SqlAnswer qa, int questionId, string answerSql)
        {
            int point = 0;
            int userId = qa.userId;
            string refsql = qa.refAnswer;
            string preSQL = "";
            try
            {
                int x = qa.refAnswer.IndexOf(";");
                refsql = qa.refAnswer.Substring(0, x).Trim();
                preSQL = qa.refAnswer.Substring(x + 1).Trim();
                if (answerSql == refsql)
                {
                    //与参考答案相同
                    qa.status = SqlAnswer.SUCCESS;
                    qa.statusStr = SqlAnswer.SUCCESS_STR1;

                    string json = "[]";
                    qa.jsonAnswer = json;
                    qa.jsonRefAnswer = json;
                    point = qa.point;
                    saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                    return;
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "参考答案语法错误:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            
            try
            {
                if (userId != 1)
                {
                    AnswerUtils.filterString(answerSql);
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "包含非法关键字:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            
            string answersql = answerSql;

            string reftablename = getTableNameByInsert(refsql);
            string answertablename = getTableNameByInsert(answersql);
            if (answertablename.Length == 0)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "语法错误！";
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            string reftbPrefix = "thinkgoref" + qa.userId + "_";
            string anstbPrefix = "thinkgoans" + qa.userId + "_";
           
            //修改之前先创建表的SQL
            string preCreateRefTable = preSQL.Replace(reftablename, reftbPrefix + reftablename);
            string preCreateAnsTable = preSQL.Replace(reftablename, anstbPrefix + reftablename);
            //修改表要执行的SQL语句
            refsql = refsql.Replace(reftablename, reftbPrefix + reftablename);
            answersql = answersql.Replace(answertablename, anstbPrefix + answertablename);

            //查询表结构的SQL语句
            string reftableSQL = "select * from " + reftbPrefix + reftablename + "";
            string answertableSQL = "select * from " + anstbPrefix + reftablename + "";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tr = conn.BeginTransaction();
                try
                {
                    //建参考表
                    SQLHelper.ExcuteSQL(conn, tr, preCreateRefTable, null, CommandType.Text);
                    //建要修改的表
                    SQLHelper.ExcuteSQL(conn, tr, preCreateAnsTable, null, CommandType.Text);
                    //执行参考的SQL
                    SQLHelper.ExcuteSQL(conn, tr, refsql, null, CommandType.Text);
                    DataTable refdt = SQLHelper.GetTable(conn, tr, reftableSQL, null, CommandType.Text);
                    qa.jsonRefAnswer = AnswerUtils.ToJson(refdt);
                    //执行作答的SQL
                    SQLHelper.ExcuteSQL(conn, tr, answersql, null, CommandType.Text);
                    DataTable ansdt = SQLHelper.GetTable(conn, tr, answertableSQL, null, CommandType.Text);
                    qa.jsonAnswer = AnswerUtils.ToJson(ansdt);
                    DataTable dt1 = ansdt.Copy();
                    DataTable dt2 = refdt.Copy();

                    tr.Rollback();
                    conn.Close();
                    
                    if (AnswerUtils.CompareDataTable(dt1, dt2))
                    {
                        qa.status = SqlAnswer.SUCCESS;
                        qa.statusStr = SqlAnswer.SUCCESS_STR2;
                        point = qa.point;
                    }
                    else
                    {
                        qa.status = SqlAnswer.FAIL_RESULT;
                        qa.statusStr = SqlAnswer.FAIL_RESULT_STR;
                        point = 0;
                    }
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    string msg = ex.Message.Replace(reftbPrefix, "");
                    msg = msg.Replace(anstbPrefix, "");
                    throw new Exception(msg);
                }
            }
            
            saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
            return;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="qa"></param>
        /// <param name="questionId"></param>
        /// <param name="answerSql"></param>
        /// <param name="preSQL">修改之前用于创建表的SQL</param>
        public void answerAlterTable(SqlAnswer qa, int questionId, string answerSql)
        {
            int point = 0;
            int userId = qa.userId;
            string refsql = qa.refAnswer;
            string preSQL = "";
            try
            {
                int x = qa.refAnswer.IndexOf(";");
                refsql = qa.refAnswer.Substring(0, x).Trim();
                preSQL = qa.refAnswer.Substring(x + 1).Trim();
                if (answerSql == refsql)
                {
                    //与参考答案相同
                    qa.status = SqlAnswer.SUCCESS;
                    qa.statusStr = SqlAnswer.SUCCESS_STR1;

                    string json = "[]";
                    qa.jsonAnswer = json;
                    qa.jsonRefAnswer = json;
                    point = qa.point;
                    saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                    return;
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "参考答案语法错误:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            
            try
            {
                if (userId != 1)
                {
                    AnswerUtils.filterString(answerSql);
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "包含非法关键字:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            
            string answersql = answerSql;

            string reftablename = getTableName(refsql);
            string answertablename = getTableName(answersql);
            if (answertablename.Length == 0)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "修改表的语法错误！";
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            string reftbPrefix = "thinkgoref" + qa.userId + "_";
            string anstbPrefix = "thinkgoans" + qa.userId + "_";
           
            //修改之前先创建表的SQL
            string preCreateRefTable = preSQL.Replace(reftablename, reftbPrefix + reftablename);
            string preCreateAnsTable = preSQL.Replace(reftablename, anstbPrefix + reftablename);
            //修改表要执行的SQL语句
            refsql = refsql.Replace(reftablename, reftbPrefix + reftablename);
            answersql = answersql.Replace(answertablename, anstbPrefix + answertablename);

            //查询表结构的SQL语句
            string reftableSQL = "select * from get_table_structure('" + reftbPrefix + reftablename + "')";
            string answertableSQL = "select * from get_table_structure('" + anstbPrefix + reftablename + "')";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tr = conn.BeginTransaction();
                try
                {
                    //建参考表
                    SQLHelper.ExcuteSQL(conn, tr, preCreateRefTable, null, CommandType.Text);
                    //建要修改的表
                    SQLHelper.ExcuteSQL(conn, tr, preCreateAnsTable, null, CommandType.Text);
                    //执行参考的SQL
                    SQLHelper.ExcuteSQL(conn, tr, refsql, null, CommandType.Text);
                    DataTable refdt = SQLHelper.GetTable(conn, tr, reftableSQL, null, CommandType.Text);
                    qa.jsonRefAnswer = AnswerUtils.ToJson(refdt);
                    //执行作答的SQL
                    SQLHelper.ExcuteSQL(conn, tr, answersql, null, CommandType.Text);
                    DataTable ansdt = SQLHelper.GetTable(conn, tr, answertableSQL, null, CommandType.Text);
                    qa.jsonAnswer = AnswerUtils.ToJson(ansdt);
                    SqlParameter p1 = new SqlParameter("@tbname1", reftbPrefix + reftablename);
                    SqlParameter p2 = new SqlParameter("@tbname2", anstbPrefix + reftablename);
                    DataTable dt = SQLHelper.GetTable(conn, tr, "p_compare_table_structure", new SqlParameter[] { p1, p2 }, CommandType.StoredProcedure);
                    if (dt.Rows.Count < 1)
                    {
                        qa.status = SqlAnswer.SUCCESS;
                        qa.statusStr = SqlAnswer.SUCCESS_STR2;
                        point = qa.point;
                    }
                    else
                    {
                        string str = "比较结果:";
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (str.Length > "比较结果:".Length) str += ";";
                            str += dr[0].ToString();

                        }
                        qa.status = SqlAnswer.FAIL_RESULT;
                        qa.statusStr = str;
                        point = 0;
                    }

                    tr.Rollback();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    string msg = ex.Message.Replace(reftbPrefix, "");
                    msg = msg.Replace(anstbPrefix, "");
                    throw new Exception(msg);
                }
            }
            
            saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
            return;
        }

        public void answerCreateTable(SqlAnswer qa, int questionId, string answerSql)
        {            
            int point = 0;
            int userId = qa.userId;
            if (answerSql == qa.refAnswer)
            {
                //与参考答案相同
                qa.status = SqlAnswer.SUCCESS;
                qa.statusStr = SqlAnswer.SUCCESS_STR1;

                try
                {
                    string json = "[]";
                    qa.jsonAnswer = json;
                    qa.jsonRefAnswer = json;
                    point = qa.point;
                }
                catch (Exception ex)
                {
                    qa.status = SqlAnswer.FAIL_COMPILE;
                    qa.statusStr = "参考答案语法错误:" + ex.Message;
                }
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;               
            }
            try
            {
                if (userId != 1)
                {
                    AnswerUtils.filterString(answerSql);
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "包含非法关键字:" + ex.Message;
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return ;
            }
            string refsql = qa.refAnswer;
            string answersql = answerSql;
            string reftablename = getTableName(refsql);
            string answertablename = getTableName(answersql);
            if (answertablename.Length == 0)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "创建表的语法错误！";
                saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return;
            }
            string reftbPrefix = "thinkgoref" + qa.userId + "_";
            string anstbPrefix = "thinkgoans" + qa.userId + "_";
            refsql = refsql.Replace(reftablename, reftbPrefix + reftablename);
            answersql = answersql.Replace(answertablename, anstbPrefix + answertablename);

            string reftableSQL = "select * from get_table_structure('" + reftbPrefix + reftablename + "')";
            string answertableSQL = "select * from get_table_structure('" + anstbPrefix + answertablename + "')";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tr = conn.BeginTransaction();
                try
                {
                    SQLHelper.ExcuteSQL(conn, tr, refsql, null, CommandType.Text);
                    DataTable refdt = SQLHelper.GetTable(conn, tr, reftableSQL, null, CommandType.Text);
                    qa.jsonRefAnswer = AnswerUtils.ToJson(refdt);
                    SQLHelper.ExcuteSQL(conn, tr, answersql, null, CommandType.Text);
                    DataTable ansdt = SQLHelper.GetTable(conn, tr, answertableSQL, null, CommandType.Text);
                    qa.jsonAnswer = AnswerUtils.ToJson(ansdt);
                    SqlParameter p1 = new SqlParameter("@tbname1", reftbPrefix + reftablename);
                    SqlParameter p2 = new SqlParameter("@tbname2", anstbPrefix + answertablename);
                    DataTable dt = SQLHelper.GetTable(conn, tr, "p_compare_table_structure", new SqlParameter[] { p1, p2 }, CommandType.StoredProcedure);
                    if (dt.Rows.Count < 1)
                    {
                        qa.status = SqlAnswer.SUCCESS;
                        qa.statusStr = SqlAnswer.SUCCESS_STR2;
                        point = qa.point;
                    }
                    else
                    {
                        string str = "比较结果:";
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (str.Length > "比较结果:".Length) str += ";";
                            str += dr[0].ToString();

                        }
                        qa.status = SqlAnswer.FAIL_RESULT;
                        qa.statusStr = str;
                        point = 0;
                    }

                    tr.Rollback();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    string msg = ex.Message.Replace(reftbPrefix, "");
                    msg = msg.Replace(anstbPrefix, "");
                    throw new Exception(msg);
                }
            }
            //point = qa.point;
            saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
            return ;
        }

        private string getTableNameIndex(string sql)
        {
            sql = sql.Replace("\n", " ").ToLower();
            Match match = Regex.Match(sql, @"index (.+?) on(.+?)(\()");

            if (match.Success)
            {
                return match.Groups[2].Value.Trim();
            }
            return "";
        }

        private string getIndexName(string sql)
        {
            sql = sql.Replace("\n", " ").ToLower();
            Match match = Regex.Match(sql, @"index (.+?) on(.+?)(\()");

            if (match.Success)
            {
                return match.Groups[1].Value.Trim();
            }
            return "";
        }
        private string getTableName(string sql)
        {
            sql = sql.Replace("\n", " ").ToLower();
            Match match = Regex.Match(sql, @"table(.+?)(\(|add|alter|drop)");

            if (match.Success)
            {
                return match.Groups[1].Value.Trim();
            }
            return "";
        }
        private string getViewName(string sql)
        {
            sql = sql.Replace("\n", " ");
            Match match = Regex.Match(sql, @"view(.+?) as");

            if (match.Success)
            {
                return match.Groups[1].Value.Trim();
            }
            return "";
        }
        private string getTableNameByInsert(string sql)
        {
            sql = sql.Replace("\n", " ").ToLower();
            Match match = Regex.Match(sql, @"(insert into|update|delete from)(.+?)(\(|values|set|where)");

            if (match.Success)
            {
                return match.Groups[2].Value.Trim();
            }            
            return "";
        }
        public DataTable select(string sql)
        {            
            return SQLHelper.GetTable(sql);
        }

        public void saveAnswerRecord(int qid,string answer,int status, string statusStr, int score, int userId)
        {
            SqlParameter[] p = new SqlParameter[]{ new SqlParameter("@questionId", qid)
                ,new SqlParameter("@answer", answer)
                ,new SqlParameter("@status", status)
                ,new SqlParameter("@statusStr", statusStr)
                ,new SqlParameter("@score", score)
                ,new SqlParameter("@userId", userId)
            };

            string sql = "insert into sqlAnswer(questionId,answer,status,statusStr,score,userId) " +
                                    "values(@questionId,@answer,@status,@statusStr,@score,@userId)";
            SQLHelper.ExcuteSQL(sql, p);            
        }

        
       
        public string test()
        {
            string sql = "create table aa(id int primary key, name varchar(10))";
            List<ColumnInfo> list = new List<ColumnInfo>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tr = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Transaction = tr;
                cmd.ExecuteNonQuery();
                DataTable schemaTable = GetReaderSchema("aa", conn, tr);

                foreach (DataRow dr in schemaTable.Rows)
                {
                    ColumnInfo info = new ColumnInfo();
                    info.Name = dr["ColumnName"].ToString();
                    info.Ordinal = Convert.ToInt32(dr["ColumnOrdinal"].ToString());
                    info.AllowDBNull = (bool)dr["AllowDBNull"];
                    info.MaxLength = Convert.ToInt32(dr["ColumnSize"].ToString());
                    info.DataTypeId = Convert.ToInt32(dr["ProviderType"].ToString());
                    info.DataType = dr["DataTypeName"].ToString().Trim();
                    info.AutoIncrement = (bool)dr["IsAutoIncrement"];
                    info.IsPrimaryKey = (bool)dr["IsKey"];
                    info.Unique = (bool)dr["IsUnique"];
                    info.IsReadOnly = (bool)dr["IsReadOnly"];
                    string netType = dr["DataType"].ToString();

                    list.Add(info);
                }
                tr.Rollback();
                conn.Close();
            }
            string json = JsonUtil.ToJson(list);
            return json;
        }
        private DataTable GetReaderSchema(string tableName, SqlConnection connection, SqlTransaction tr = null)
        {
            DataTable schemaTable = null;
            IDbCommand cmd = new SqlCommand();
            if (tr != null) cmd.Transaction = tr;
            cmd.CommandText = string.Format("select * from [{0}]", tableName);
            cmd.Connection = connection;

            using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
            {
                schemaTable = reader.GetSchemaTable();
            }
            return schemaTable;
        }

    }
}